<?php

use yii\db\Migration;

class m170906_135007_init_dashou_table extends Migration
{
    public function up()
    {
        $this->createPostGroup();
        $this->createPostGroupAudit();
        $this->createPostCategory();
        $this->createPostGroupCategoryRelation();
        $this->createUserJoinGroup();
        $this->createPost();
        $this->createComment();
    }

    private function createPostGroup() {
        /**
        post_group
        |- id group_name cover intro keywords owner_uid create_at state
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `post_group` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`group_name` varchar(255),
`cover` varchar(2048),
`intro` varchar(2048),
`keywords` varchar(2048),
`owner_uid` bigint(20),
`create_at` int(11) DEFAULT 0,
`state` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE (`group_name`),
INDEX (`intro`),
INDEX (`keywords`),
INDEX (`owner_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createPostGroupAudit() {
        /**
        post_group_audit
        |- id group_name cover intro keywords owner_uid create_at audit_state audit_desc
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `post_group_audit` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`group_name` varchar(255),
`cover` varchar(2048),
`intro` varchar(2048),
`keywords` varchar(2048),
`owner_uid` bigint(20),
`category_ids` varchar(2048),
`create_at` int(11) DEFAULT 0,
`audit_state` int(11) DEFAULT 0,
`audit_desc` varchar(2048),
PRIMARY KEY (`id`),
UNIQUE (`group_name`),
INDEX (`owner_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createPostCategory() {
        /**
        post_category
        |- id name desc
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `post_category` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`name` varchar(255),
`desc` varchar(2048),
PRIMARY KEY (`id`),
UNIQUE (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createPostGroupCategoryRelation() {
        /**
        post_group_category_relation
        |- id gid cid
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `post_group_category_relation` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`gid` bigint(20),
`cid` bigint(20),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createUserJoinGroup() {
        /**
        user_join_group
        |- uid gid state
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `user_join_group` (
`uid` bigint(20),
`gid` bigint(20),
`state` int(11) DEFAULT 0,
PRIMARY KEY (`uid`, `gid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createPost() {
        /**
        post
        |- id gid uid title content post_url floor create_at update_at
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `post` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`gid` bigint(20),
`uid` bigint(20),
`title` varchar(255),
`content` varchar(65530),
`post_url` varchar(2048),
`floor` int(11) DEFAULT 0,
`create_at` int(11) DEFAULT 0,
`update_at` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
INDEX (`gid`),
INDEX (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    private function createComment() {
        /**
        comment
        |- id post_id parent_cid content floor create_at
         */
        $sql = <<<EOD
CREATE TABLE IF NOT EXISTS `comment` (
`id` bigint(20) unsigned AUTO_INCREMENT,
`post_id` bigint(20),
`parent_cid` bigint(20),
`content` varchar(65530),
`floor` int(11) DEFAULT 0,
`create_at` int(11) DEFAULT 0,
PRIMARY KEY (`id`),
INDEX (`post_id`),
INDEX (`parent_cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
EOD;
        $this->db->createCommand($sql)->execute();
    }

    public function down()
    {
        return false;
    }

    /*
    // Use safeUp/safeDown to run migration code within a transaction
    public function safeUp()
    {
    }

    public function safeDown()
    {
    }
    */
}
